package com.bluesky.light.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * 為常用的SQL語句寫的一些接口, 在程序其他部分更容易存取數據庫
 * 
 * @author
 * 
 */
public class DB {

	/**
	 * @return conn
	 * 
	 *         得到數據庫的連接
	 */
	public static Connection getConn() {
		Connection conn = null;
		try {
			/* connect to local mysql */
			/*
			 * Class.forName("com.mysql.jdbc.Driver"); conn = DriverManager
			 * .getConnection
			 * ("jdbc:mysql://localhost:3306/light?user=root&password=root");
			 */

			/* connect to hust db2 */
			/*
			 * Class.forName("com.ibm.db2.jcc.DB2Driver"); String url=""; String
			 * user=""; String password=""; conn =
			 * DriverManager.getConnection(url,user,password);
			 */

			/* connect to local db2 */
			Class.forName("com.ibm.db2.jcc.DB2Driver");
			String url = "jdbc:db2://192.168.1.110:50000/light";
			String user = "db2admin";
			String password = "vsx46xa6";
			conn = DriverManager.getConnection(url, user, password);

		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return conn;
	}

	/**
	 * @param conn
	 * @param sql
	 * @return stmt
	 * 
	 *         主要是用作UPDATE 和 插入數據到數據庫的準備
	 */
	public static PreparedStatement prepare(Connection conn, String sql) {
		PreparedStatement pstmt = null;
		try {
			if (conn != null) {
				pstmt = conn.prepareStatement(sql);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return pstmt;
	}

	/**
	 * @param conn
	 * @param sql
	 * @param autoGeneratedKeys
	 * @return
	 * 
	 *         主要是用作插入, 更新數據庫, 而且可以在程序部分輸入SQL 語句的參數
	 */
	public static PreparedStatement prepare(Connection conn, String sql,
			int autoGeneratedKeys) {
		PreparedStatement pstmt = null;
		try {
			if (conn != null) {
				pstmt = conn.prepareStatement(sql, autoGeneratedKeys);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return pstmt;
	}

	/**
	 * @param conn
	 * @return stmt
	 * 
	 *         主要是在查詢語句使用
	 */
	public static Statement getStatement(Connection conn) {
		Statement stmt = null;
		try {
			if (conn != null) {
				stmt = conn.createStatement();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return stmt;
	}

	/**
	 * @param stmt
	 * @param sql
	 * @return
	 * 
	 * 
	 */
	public static ResultSet getResultSet(Statement stmt, String sql) {
		ResultSet rs = null;
		try {
			if (stmt != null) {
				rs = stmt.executeQuery(sql);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rs;
	}

	/**
	 * @param stmt
	 * @param sql
	 * 
	 *            更改或者插入數據到數據庫
	 */
	public static void excuteUpdate(Statement stmt, String sql) {
		try {
			if (stmt != null) {
				stmt.executeUpdate(sql);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * @param conn
	 * 
	 *            關閉數據庫的連接
	 */
	public static void close(Connection conn) {
		try {
			if (conn != null) {
				conn.close();
				conn = null;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * @param stmt
	 * 
	 *            關閉statement對象
	 */
	public static void close(Statement stmt) {
		try {
			if (stmt != null) {
				stmt.close();
				stmt = null;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * @param rs
	 * 
	 *            關閉查詢數據庫的結果集合
	 */
	public static void close(ResultSet rs) {
		try {
			if (rs != null) {
				rs.close();
				rs = null;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}
